rm(list=ls())
library(tidyverse)
library(readxl)

# Read and structreu PEA for census 1999,2000,2010
file <- list.files(here::here("data","raw","IBGE"), pattern = "tab136_mun_population_by_race", full.names = T)

race_mun_1991 <- read_excel(file,
                           sheet = "1991",
                           skip = 3,
                           col_names = c("mun_code","mun_name","var_desc","race","white_perc"),
                           col_types = c(rep("text",4),"numeric"),
                           na = "...") %>%
  filter(!is.na(mun_name)) %>% 
  mutate(census_year = 1991,
         mun_code = substring(mun_code,1,6)) %>% 
  select(mun_code,mun_name,census_year,white_perc)

race_mun_2000 <- read_excel(file,
                            sheet = "2000",
                            skip = 3,
                            col_names = c("mun_code","mun_name","var_desc","race","white_perc"),
                            col_types = c(rep("text",4),"numeric"),
                            na = "...") %>% 
  filter(!is.na(mun_name)) %>% 
  mutate(census_year = 2000,
         mun_code = substring(mun_code,1,6)) %>% 
  select(mun_code,mun_name,census_year,white_perc)

race_mun_2010 <- read_excel(file,
                            sheet = "2010",
                            skip = 3,
                            col_names = c("mun_code","mun_name","var_desc","race","white_perc"),
                            col_types = c(rep("text",4),"numeric"),
                            na = "...") %>% 
  filter(!is.na(mun_name)) %>% 
  mutate(census_year = 2010,
         mun_code = substring(mun_code,1,6)) %>% 
  select(mun_code,mun_name,census_year,white_perc)

race_mun <- bind_rows(race_mun_1991,race_mun_2000,race_mun_2010)


# SaveRdS ----

write_rds(race_mun,here::here("data","processed","citycharacteristics","race_mun.rds"))
